// 引入mysql模块
var models = require('../db');
const mysql = require('mysql');
var express = require('express');
var router = express.Router();
var conn = mysql.createConnection(models.mysql);


// 连接数据库
conn.connect((err) => {
  if (err) {
    console.error('Error connecting to database: ' + err.stack);
    return;
  }
  console.log('Connected to database as id ' + conn.threadId);
});
//已经执行到这里了



// 查询某个表格的数据
conn.query('SELECT * FROM zhilingqitao', (error, results, fields) => {
  if (error) throw error;
  
  
  // 处理查询结果
  results.forEach((item) => {
    // 计算齐套时间
    //const max_time={ value: 0 }; 
   let max_time=0;
   let wei=0;
   let x;
   let sum=0;
   let sum1=0;

   function convertToPercent(number) {  
    return `${(number * 100).toFixed(2)}%`;  
  }  

    //////////////////////
    conn.query('SELECT * FROM wuliaoqitao', (error, results1, fields) => {
        if (error) throw error;
        
        
        // 处理查询结果
        results1.forEach((item1) => {
          // 计算齐套时间
         // console.log(`new value = ${max_time.value}`);
         /* if(item.wuliao1 === item1.wuliao_number){
          max_time.value = item1.Remaining_time_for_completion;
          console.log(`new value = ${max_time.value}`);
          

        }*/
         // const condition2 = max_time < item1.Remaining_time_for_completion;
        if(item.wuliao1 === item1.wuliao_number&&max_time<item1.Remaining_time_for_completion){
            max_time = item1.Remaining_time_for_completion;
            console.log(`new value = ${max_time}`);
            
  
          }

          else if(item.wuliao2 === item1.wuliao_number&&max_time<item1.Remaining_time_for_completion){
            max_time= item1.Remaining_time_for_completion;
            console.log(`new value = ${max_time}`);
            
  
          }
          //item1.Remaining_time_for_completion
          else if(item.wuliao3 === item1.wuliao_number&&max_time<item1.Remaining_time_for_completion){
           
            max_time = item1.Remaining_time_for_completion;
            console.log(`new value = ${item1.Remaining_time_for_completion}`);
            
            
  
          }
          
          else if(item.wuliao4 === item1.wuliao_number&&max_time<item1.Remaining_time_for_completion){
            max_time = item1.Remaining_time_for_completion;
            console.log(`new value = ${max_time}`);
            
  
          }
          else if(item.wuliao5 === item1.wuliao_number&&max_time<item1.Remaining_time_for_completion){
            max_time = item1.Remaining_time_for_completion;
            console.log(`new value = ${max_time}`);
            
  
          }
          else if(item.wuliao6 === item1.wuliao_number&&max_time<item1.Remaining_time_for_completion){
            max_time = item1.Remaining_time_for_completion;
            console.log(`new value = ${max_time}`);
            
  
          }
          else if(item.wuliao7 === item1.wuliao_number&&max_time<item1.Remaining_time_for_completion){
            max_time = item1.Remaining_time_for_completion;
            console.log(`new value = ${max_time}`);
            
  
          }
          else if(item.wuliao8 === item1.wuliao_number&&max_time<item1.Remaining_time_for_completion){
            max_time = item1.Remaining_time_for_completion;
            console.log(`new value = ${max_time}`);
            
  
          }
          else if(item.wuliao9 === item1.wuliao_number&&max_time<item1.Remaining_time_for_completion){
            max_time = item1.Remaining_time_for_completion;
            console.log(`new value = ${max_time}`);
            
  
          }
          else if(item.wuliao10 === item1.wuliao_number&&max_time<item1.Remaining_time_for_completion){
            max_time = item1.Remaining_time_for_completion;
            console.log(`new value = ${max_time}`);
            
  
          }

          if(item.wuliao1 === item1.wuliao_number&&item1.Remaining_time_for_completion>0&&item1.necessary==="是")
          {wei++;}
          else if(item.wuliao2 === item1.wuliao_number&&item1.Remaining_time_for_completion>0&&item1.necessary==="是")
            {wei++;}
           else if(item.wuliao3 === item1.wuliao_number&&item1.Remaining_time_for_completion>0&&item1.necessary==="是")
            {wei++;}
           else if(item.wuliao4 === item1.wuliao_number&&item1.Remaining_time_for_completion>0&&item1.necessary==="是")
            {wei++;}
           else if(item.wuliao5 === item1.wuliao_number&&item1.Remaining_time_for_completion>0&&item1.necessary==="是")
            {wei++;}
           else if(item.wuliao6 === item1.wuliao_number&&item1.Remaining_time_for_completion>0&&item1.necessary==="是")
            {wei++;}
           else if(item.wuliao7 === item1.wuliao_number&&item1.Remaining_time_for_completion>0&&item1.necessary==="是")
            {wei++;}
           else if(item.wuliao8 === item1.wuliao_number&&item1.Remaining_time_for_completion>0&&item1.necessary==="是")
            {wei++;}
           else  if(item.wuliao9 === item1.wuliao_number&&item1.Remaining_time_for_completion>0&&item1.necessary==="是")
            {wei++;}
           else if(item.wuliao10 === item1.wuliao_number&&item1.Remaining_time_for_completion>0&&item1.necessary==="是")
            {wei++;}


           if(item.wuliao1 === item1.wuliao_number)
            {sum++;
                if(item1.Remaining_time_for_completion>0)
                {sum1++;}
            }
            if(item.wuliao2 === item1.wuliao_number)
                {sum++;
                    if(item1.Remaining_time_for_completion>0)
                    {sum1++;}
                }
                if(item.wuliao3 === item1.wuliao_number)
                    {sum++;
                        if(item1.Remaining_time_for_completion>0)
                        {sum1++;}
                    }
                    if(item.wuliao4 === item1.wuliao_number)
                        {sum++;
                            if(item1.Remaining_time_for_completion>0)
                            {sum1++;}
                        }
                        if(item.wuliao5 === item1.wuliao_number)
                            {sum++;
                                if(item1.Remaining_time_for_completion>0)
                                {sum1++;}
                            }
                            if(item.wuliao6 === item1.wuliao_number)
                                {sum++;
                                    if(item1.Remaining_time_for_completion>0)
                                    {sum1++;}
                                }
                                if(item.wuliao7 === item1.wuliao_number)
                                    {sum++;
                                        if(item1.Remaining_time_for_completion>0)
                                        {sum1++;}
                                    }
                                    if(item.wuliao8 === item1.wuliao_number)
                                        {sum++;
                                            if(item1.Remaining_time_for_completion>0)
                                            {sum1++;}
                                        }
                                        if(item.wuliao9 === item1.wuliao_number)
                                            {sum++;
                                                if(item1.Remaining_time_for_completion>0)
                                                {sum1++;}
                                            }
                                            if(item.wuliao10 === item1.wuliao_number)
                                                {sum++;
                                                    if(item1.Remaining_time_for_completion>0)
                                                    {sum1++;}
                                                }
           
          //console.log(`1new value = ${max_time}`);
       // &&max_time.value<item1.Remaining_time_for_completion
            
        });
      
      if(wei===0){x="否";}
      else{x="是";}
      
      let rate1=((sum-sum1)/sum);
        let rate=convertToPercent(rate1)
      console.log(sum,sum1,rate)

          // 更新数据库中的数据
    const updateSQL = 'UPDATE zhilingqitao SET qitaotime= ?,necessary_parts=?,qitao_rate=?  WHERE number = ?';
    const updateData = [max_time,x,rate,item.number]; 
    //console.log(`1new value = ${max_time}`);
    console.log(wei)

    conn.query(updateSQL, updateData, (err, result) => {
       // console.log(`2new value = ${max_time}`);
      if (err) throw err;
      console.log(`Updated item ${item.number}: new value = ${max_time}`);
      //console.log(max_time);
      // 结束数据库连接（没写）

    });


    
      });
    
////////////////////////
   
  

  


  });


});

module.exports = router;
